Snowflakeのロール階層設計ちゃんとしてますか？
2024.08.16データ事業本部の荒木です。
Snowflakeのロールを業務で運用していると、ロールの数が多くなったり、どのロールにどの権限がついているか分かりづらくなり管理が大変になったりすることあるかと思います。
実際に私も運用していく中で、Snowflakeのロールや権限は自由度が高くどういう設定をしたらいいのだろう。。と悩んでました。
公式ドキュメントにもロールの推奨構成について記載していますが、サンプル構成など具体的な構成で参考文献があまりなかったので、色々検討したSnowflakeのロール構成について紹介したいと思います。
Snowflakeロールについて
まずSnowflakeのロールについて簡単に説明します。
ロールとは
Snowflakeのロールは、「セキュリティ保護可能なオブジェクトに対する権限の付与や取り消しができるエンティティ」を意味しています。
要はSnowflake内に作成されるオブジェクトに対する権限をロールに付与し、そのロールをユーザに割り当てることでユーザが許可されたオブジェクトへアクセスすることができるようになります。
ユーザに対して直接権限を付与することはできないため、権限割り当てはロールを経由して行う必要があります。
ロールタイプについて
Snowflakeのロールは、大きく分けて以下2つのタイプがあります。
システム定義のロール
システム定義のロールは、権限付与やオブジェクト作成などアカウント管理を目的として予め作成されているロールです。
- ORGADMIN
- 組織レベルで運用を管理するロール
- ACCOUNTADMIN
- システムの最上位のロール
- SECURITYADMIN
- オブジェクトの付与をグローバルに管理し、ユーザーとロールを作成、モニター、管理できるロール
- USERADMIN
- ユーザーとロールの管理のみに専用のロール
- SYSADMIN
- アカウントでウェアハウスとデータベース（およびその他のオブジェクト）を作成する権限を持つロール
- PUBLIC
- アカウント内のすべてのユーザーおよびすべてのロールに自動的に付与される疑似ロール
権限としては用途に応じた強めの権限が設定されているため、限られたユーザーのみが使用するロールとなります。
ベストプラクティスとして、システム定義のロールはアカウント管理権限を扱うため、エンティティ固有の権限などを同じロールに混在させることは推奨されていません。
追加の権限が必要な場合、後述するカスタムロールに追加の権限を付与し、システム定義のロールにカスタムロールを割り当てることが推奨されています。
なんでもかんでもシステム定義ロールでオブジェクト作成したり権限を付与したりせずに必要な権限を付与したカスタムロールを作って作業して、システム定義ロールに権限継承してねってことです。
カスタムロール
ユーザーが作成できるロールにもいくつか種類があります。
今回の設計で使用している2つのロールの特徴をまとめます。
-
アカウントロール
- アカウント内の任意のオブジェクトに対してSQLアクションを許可することができる
- ユーザーがセッションで直接アクティブ化できる
- Snowsightのロールツリー構造に表示される
-
データベースロール
- SQL アクションを1つのデータベースとデータベース内の任意のオブジェクトに制限することができる
- データベースに作成されるロールであるため、他のデータベースに関連する権限などを付与することはできない
- グローバル権限やアカウントオブジェクト権限などを付与することができない
- ユーザーがセッションで直接アクティブ化できないので、アカウントロールに継承する必要がある
- Snowsightのロールツリー構造に表示されない
アカウントロールにはSnowflake内のオブジェクト全てに関連する権限を付与できますが、データベースロールは作成するデータベースロールと同じデータベース配下のオブジェクトの権限のみ付与できます。
データベースロールでできることは、基本アカウントロールでもできるので必ずしもデータベースロールを使う必要はないです。
個人的にデータベースロール使っていいなと思ったのは、ロールの数が多くなり階層構造を全てアカウントロールで構築すると、Snowsightに表示されるロールツリー構造が複雑になりとても見づらいので、権限だけを付与したロールを作成する場合などにSnowsight表示されないデータベースロールを使うことで表示を見やすくできるかなと思います。
↑ここにデータベースロールは表示されないです
権限継承について
Snowflakeのロールはユーザだけでなく、ロールに対しても割り当てることが可能です。
ロールに対してロールを割り当てると、割り当て先のロールに付与された権限に加えて割り当て元のロールに付与された権限も継承されます。
例えば図のように、テーブルAを参照のみできるロールAを作成します。
このロールAをユーザーAに割り当てると、ユーザーAはテーブルAを参照することができるようになります。
それとは別にユーザーBには削除権限も付与したいとなった場合、ロールAに付与してしまうとユーザーAにも削除権限が付与されてしまいます。
テーブルAの参照権限と削除権限を付与したロールBを別で作成する必要がありますが、付与する権限が多くなると権限を追加で付与したいとなる度に、ロールを作成し権限を付与する必要があり運用が大変になります。
そのため新しく作成するロールBには既存と同じロールの権限を付与する場合には必要な権限を全て付与するのではなく、付与したい権限が既にあるロールAを割り当てることで権限を継承することができます。
これによりロールBに削除権限だけを付与することで、テーブルAの参照権限と削除権限がロールBを割り当てたユーザーBに対して付与することができます。
このロール階層構造を構築することで柔軟な権限設定や権限管理を実施することが可能となります。
ただ柔軟であるが故に階層構造での権限設定方針などを予め決めておかないと、後々不要なロール作成や権限付与設定、意図しない権限付与などの問題が発生してしまいます。
以上がロール設計するにあたり押さえておきたいポイントです。
前提条件
次にロール設計するにあたり、ロール構築後の権限管理を容易にするためのいくつかの前提条件を決めました。
管理アクセススキーマについて
Snowflakeではセキュリティ向上のための権限管理を実施することができる管理アクセススキーマを使用することができます。
通常スキーマを作成した場合、スキーマ内のオブジェクト（テーブルなど）の権限はテーブルを作成したロールがOWNER権限を所有することになります。
この場合そのオブジェクトに対する権限付与をオブジェクトのOWNER権限を持っているロールで行うことができます。
CREATE権限をカスタムロールに付与して運用していく場合、オブジェクトのOWNER権限は各ロールに付与されていくため、OWNER権限があるオブジェクトへの権限を他のロールにも勝手に付与できてしまうため、OWNER権限の管理方法を考えずに通常スキーマで運用することはあまりよくありません。
しかし管理アクセススキーマを使用すると、オブジェクトへの権限付与操作をオブジェクトのOWNER権限を持つロールではなく、オブジェクトが作成されているスキーマのOWNER権限を持っているロールにすることができます。
※オブジェクトのOWNER権限を所有しているロール以外に、SECURITYADMIN以上またはMANAGE GRANTS権限のある任意のロールは、通常スキーマと管理アクセススキーマのどちらでも権限付与可能です。
この管理アクセススキーマを使用することでオブジェクトのOWNER権限の所在について深く考える必要がなくなるため今回のロール設計では 管理アクセススキーマを使用すること を前提としています。
管理アクセススキーマの設定方法については以下の記事もご参照ください。
OWNER権限の所有者について
前述でOWNER権限については深く考えないと述べましたが、ある程度の所在についてはルール決めを行ないます。
今回考えたロール設計ではSYSADMINでのオブジェクト作成を実施する想定ではないため、オブジェクトの作成はCREATE権限を付与したカスタムロールで管理することとしています。
そのためOWNER権限はオブジェクトを作成したカスタムロールで管理します。
データベースやスキーマのOWNER権限は管理者用カスタムロールで管理し、テーブルなどスキーマ配下オブジェクトは一般用カスタムロールで管理します。（詳しくは後述します）
将来権限について
今回データベースロールに対して、将来の権限付与も設定します。
ただし、データベースロールに対して将来の権限付与を行なう場合には、そのデータベースロールをData Sharingによってデータベースロールの権限を共有することができません。
Data Sharingの使用を検討している場合は、将来の権限付与を行なうロールについて検討が必要となります。
データベースロール: 将来の付与を使用したデータベースロールの共有禁止
ロール設計仕様
さて本題です。
今回のロール設計ですが、「 オブジェクト権限付与の運用・管理の簡素化 」を実現することをコンセプトとしました。
ユーザーに割り当てるロールと権限を付与するロールを分けて管理するようにし、権限付与用のロールはなるべく細かく設定します。そして同じオブジェクトに対する権限は階層構造とすることで無駄な権限付与を行なわないようにしています。
細かく権限を設定することで、ユーザーに対して最小限の権限設定することも可能です。
ロールの構成図は以下になります。
階層ごとのロールの用途や付与する権限についてを以下にまとめます。
システム階層
この階層は限られたユーザーだけが使用できるロールがある階層です。
アカウント管理者がアカウント管理のためシステム定義のロールを使用して操作します。
- ACCOUNTADMIN
- アカウント管理権限やグローバル権限などをカスタムロールに付与する必要がある場合のみ使用します
- SECURITYADMIN
- オブジェクトへの権限付与をシステム管理者権限とし、カスタムロールに対して権限付与はこのロールを使用します（以下は例外とします）
- MANAGE GRANTS権限を付与したカスタムロールを運用する場合での権限付与
- データベース内オブジェクトのOWNER権限を所有するカスタムロールによる権限付与
- オブジェクトへの権限付与をシステム管理者権限とし、カスタムロールに対して権限付与はこのロールを使用します（以下は例外とします）
- SYSADMIN
- 基本的にこのロールを使用してオブジェクトを作成せず、オブジェクト作成に必要な権限を付与したカスタムロールを作成します
- オブジェクト管理の最上位ロールとして、全てのカスタムロールはこのロールに対して継承されるように設定します
SYSADMINに全てのカスタムロールを継承するとしていますが、継承したカスタムロールに本来SYSADMINが付与されていないアカウント管理権限やグローバル権限などの権限が付与されている場合SYSADMINに権限継承されるのはどうなのかなーという気はあります。。。
ただSnowflakeからは全てのカスタムロールはSYSADMINに継承することが推奨されていますし、SYSADMINは階層構造のカスタムロールの権限全てを持ったスーパーユーザー用ロールと考えればありなのかなと考えました。
もちろん本来ACCOUNTADMINしかもっていない権限を付与したロールはACCOUNTADMINに継承するなどのルールで継承設定するのもありなのかなと思います。
管理者階層
この階層はアカウント管理者によって払い出されたアカウント管理権限やグローバル権限などが付与されたロールがある階層です。
データベースオブジェクト管理者や開発者などが一部管理者権限を必要とするため、アカウント管理権限やグローバル権限が付与されたアカウントロールを使用して操作します。
- USERADMIN
- システム定義のロールで、ユーザーやロールを作成するために使用します
- 管理者としてユーザーやロールを作成する権限を付与したい場合、このロールを割り当てます
- DATABASE_ADMIN_ROLE
- データベース管理者の利用を想定したアカウントロールです
- 全ての一般ユーザー階層のアカウントロールは、このロールに継承します
- データベースやスキーマのOWNER権限を所有します
- 管理アクセススキーマによりスキーマ配下オブジェクトへの権限付与操作を行ないます
- データベースやスキーマの作成や設定変更などを行ないます
- データベース内のオブジェクトの権限付与を行ないます
- 付与する権限
- CREATE DATABASE
- CREATE SCHEMA
- MANAGE GRANTS
- WAREHOUSE_ADMIN_ROLE
- データベース管理者の利用を想定したアカウントロールです
- ウェアハウスのOWNER権限を所有します
- ウェアハウスの作成や設定変更などを行ないます
- ウェアハウスの使用権限付与を行ないます
- 付与する権限
- CREATE WAREHOUSE
- INTEGRATION_ADMIN_ROLE
- INTEGRATION権限を使用する開発者の利用を想定したアカウントロールです
- 管理者としてのグローバル権限はこのロールに直接付与します
- データベースオブジェクトの権限は、付与したい権限を持つ一般ユーザー階層のアカウントロールを継承します
- 付与する権限
- CREATE INTEGRATION
DATABASE_ADMIN_ROLEにMANAGE GRANTSの権限を付与しています。
これは将来の権限付与するためにMANAGE GRANTSの権限が必要であり、SECURITYADMINなどで設定する必要がありますがSECURITYADMINにはデータベースロールへのアクセス権限がないため、DATABASE_ADMIN_ROLEにMANAGE GRANTS権限を付与しています。
ただDATABASE_ADMIN_ROLEにSECURITYADMINと同じ権限付与の権限を与えてしまうので改善の余地ありかなーと思ってます。。。
一般ユーザ階層
この階層はデータベースオブジェクトへの権限などが付与されたロールがある階層です。
一般ユーザーがSnowflakeオブジェクトに対するアクセス権限を必要とするため、用途に応じたオブジェクト権限が付与されたアカウントロールを使用して操作します。
- VIEW_ROLE_A,B
- 閲覧権限を持つ一般ユーザーの利用を想定したアカウントロールです
- 割り当てられたデータベースロールに付与された権限を継承します
- ウェアハウスの権限はこのロールに直接付与します
- 付与する権限
- USAGE（ウェアハウス）
- EDIT_ROLE_C,D
- 編集権限を持つ一般ユーザーの利用を想定したアカウントロールです
- 割り当てられたデータベースロールに付与された権限を継承します
- ウェアハウスの権限はこのロールに直接付与します
- 付与する権限
- USAGE（ウェアハウス）
- CREATE_ROLE_E,F
- 作成権限を持つ一般ユーザーの利用を想定したアカウントロールです
- 割り当てられたデータベースロールに付与された権限を継承します
- ウェアハウスの権限はこのロールに直接付与します
- 付与する権限
- USAGE（ウェアハウス）
基本的に一般ユーザーのアカウントロールは、他の一般ユーザーのアカウントロールに継承することは想定していません。ただ全く同じ権限と追加でデータベースロールから権限を付与したロールを作成したい場合などに継承するのは良いかなと考えてます。
権限付与階層
この階層はデータベースオブジェクトの権限管理を設定しているロールがある階層です。
カスタムロールに付与するデータベースオブジェクトへのアクセス権限は、データベースロールに対して権限を細かく設定しロール階層を構築することで、用途に応じた細かい権限設定を可能にします。
付与する権限の単位によって構成は変わってきますが、今回はデータベースロールへの権限付与はスキーマ単位の想定です。
DB権限（共通）
データベースに関する権限で、共通で継承したい権限のみを付与します。
- DB_A_PRIVILEGE,DB_B_PRIVILEGE
- データベースに関する権限を付与するデータベースロールです
- 付与する権限
- USAGE（データベース）
- MONITOR（データベース）
データベースロールにはデータベースのUSAGE権限は自動で付与されるので、USAGE権限しか付与する必要が無い場合には作成する必要はありません。
USAGE以外の権限やスキーマ単位ではなくデータベース単位でデータベースロールを準備する場合、スキーマUSAGE権限の将来の権限付与などの設定にも有用かなと考えています。
SCHEMA権限（共通）
スキーマに関する権限で、共通で継承したい権限のみを付与します。
基本的には参照と編集、作成の3種類の権限タイプに分けた設計にしていますが、用途に応じて増やしたり減らしたりします。
-
SCHEMA_A_VIEW_PRIVILEGE, SCHEMA_B_VIEW_PRIVILEGE, SCHEMA_C_VIEW_PRIVILEGE
- スキーマに関する参照権限を付与するデータベースロールです
- スキーマが作成されたデータベースのデータベースロールを継承します
- 付与する権限
- USAGE（スキーマ）
- SELECT & SELECT（FUTURE） & REFERENCES（TABLE）
- SELECT VIEW & SELECT VIEW（FUTURE） & REFERENCES（VIEW）
- SELECT MATERIALIZED VIEW & SELECT MATERIALIZED VIEW（FUTURE） & REFERENCES（MATERIALIZED VIEW）
-
SCHEMA_A_EDIT_PRIVILEGE, SCHEMA_B_EDIT_PRIVILEGE, SCHEMA_C_EDIT_PRIVILEGE
- スキーマに関する編集権限を付与するデータベースロールです
- 同じスキーマの参照権限用のデータベースロールを継承します
- 付与する権限
- INSERT & INSERT（FUTURE）
- UPDATE & UPDATE（FUTURE）
- DELETE & DELETE（FUTURE）
- TRUNCATE & TRUNCATE（FUTURE）
-
SCHEMA_A_CREATE_PRIVILEGE, SCHEMA_B_CREATE_PRIVILEGE, SCHEMA_C_CREATE_PRIVILEGE
- スキーマに関する作成権限を付与するデータベースロールです
- 同じスキーマの編集権限用のデータベースロールを継承します
- 付与する権限
- CREATE TABLE
- CREATE VIEW
- CREATE MATERIALIZED VIEW
必要に応じて以下のテーブルなどの権限も付与したりすることも想定しています。（今回は省略してます）
DYNAMIC TABLE, EVENT TABLE, HYBRID TABLE, ICEBERG TABLE
TABLE権限（共通）
この階層はスキーマ単位での権限付与でなく、テーブル単位で権限付与を実施したい場合に有効です。
※ただしテーブル単位での権限管理は、ロールの数が多くなり設定などが複雑になるためあまりお勧めはしません。
- TABLE_A_VIEW_PRIVILEGE, TABLE_B_VIEW_PRIVILEGE
- テーブルに関する参照権限を付与するデータベースロールです
- テーブルを作成したスキーマの参照権限用のデータベースロールを継承します
- この場合に継承するスキーマのデータベースロールは、USAGE権限のみを付与したロールを継承する
- テーブルに関する参照権限で、共通で継承したい権限のみを付与します
- 付与する権限
- SELECT
OTHER権限（非共通）
この階層はスキーマ内のオブジェクトなどで、共通ではなく一部のロールにだけ権限を付与したい場合などに個別で権限付与を行なうためのロールを作成する階層です。
サンプルとしてデータベースAの全てのスキーマに対する外部ステージを作成する権限を付与したロールと、データベースAのスキーマAに対してパイプを作成する権限を付与したデータベースロールを想定して作成します。
-
SCHEMA_A_PIPE_PRIVILEGE
- パイプに関する権限を付与するデータベースロールです
- パイプに関する権限を付与したいアカウントロールに継承します
- 付与する権限
- CREATE PIPE
-
DB_A_STAGE_PRIVILEGE
- 外部ステージに関する権限を付与するデータベースロールです
- 外部ステージに関する権限を付与したいアカウントロールに継承します
- 付与する権限
- USAGE（ステージ）
- FUTURE USAGE （ステージ）
- CREATE STAGE
スキーマのUSAGE権限も必要ですが、共通のデータベースロールから継承するのでこれらのロールには権限を付与しない想定です。
SQLチートシート
今回のロール構成を実用的にした構成（TABLEの権限階層を削除）のクエリを以下にまとめました。
作業時のユーザーは既に作成されており、各システム定義ロールに割り当てられている想定です。
データベース、スキーマ、ウェアハウスは必要に応じて作成してください。
-- ##アカウントロール作成
USE ROLE USERADMIN;
-- 管理者ユーザー階層のアカウントロール作成
CREATE ROLE DATABASE_ADMIN_ROLE;
CREATE ROLE WAREHOUSE_ADMIN_ROLE;
CREATE ROLE INTEGRATION_ADMIN_ROLE;
-- 一般ユーザー階層のアカウントロール作成
CREATE ROLE VIEW_ROLE_A;
CREATE ROLE VIEW_ROLE_B;
CREATE ROLE EDIT_ROLE_C;
CREATE ROLE EDIT_ROLE_D;
CREATE ROLE CREATE_ROLE_E;
CREATE ROLE CREATE_ROLE_F;
-- ##ロール割り当て
-- ログインしているユーザーに管理者用ロールを割り当て
GRANT ROLE DATABASE_ADMIN_ROLE TO USER "<ユーザー名>";
GRANT ROLE WAREHOUSE_ADMIN_ROLE TO USER "<ユーザー名>";
-- ##データベースロール作成
-- データベースA
USE ROLE DATABASE_ADMIN_ROLE;
USE DATABASE <データベース名A>;
-- データベース権限のデータベースロール作成
CREATE DATABASE ROLE DB_A_PRIVILEGE;
-- スキーマ権限のデータベースロール作成
CREATE DATABASE ROLE SCHEMA_A_VIEW_PRIVILEGE;
CREATE DATABASE ROLE SCHEMA_B_VIEW_PRIVILEGE;
CREATE DATABASE ROLE SCHEMA_A_EDIT_PRIVILEGE;
CREATE DATABASE ROLE SCHEMA_B_EDIT_PRIVILEGE;
CREATE DATABASE ROLE SCHEMA_A_CREATE_PRIVILEGE;
CREATE DATABASE ROLE SCHEMA_B_CREATE_PRIVILEGE;
-- その他権限のデータベースロール作成
CREATE DATABASE ROLE SCHEMA_A_PIPE_PRIVILEGE;
CREATE DATABASE ROLE DB_A_STAGE_PRIVILEGE;
-- データベースB
USE DATABASE <データベース名B>;
-- データベース権限のデータベースロール作成
CREATE DATABASE ROLE DB_B_PRIVILEGE;
-- スキーマ権限のデータベースロール作成
CREATE DATABASE ROLE SCHEMA_C_VIEW_PRIVILEGE;
CREATE DATABASE ROLE SCHEMA_C_EDIT_PRIVILEGE;
CREATE DATABASE ROLE SCHEMA_C_CREATE_PRIVILEGE;
-- ##権限付与
USE ROLE ACCOUNTADMIN;
-- 管理者ユーザー階層のDATABASE_ADMIN_ROLEに権限付与
GRANT CREATE DATABASE ON ACCOUNT TO ROLE DATABASE_ADMIN_ROLE;
GRANT MANAGE GRANTS ON ACCOUNT TO ROLE DATABASE_ADMIN_ROLE;
-- スキーマ作成権限
GRANT CREATE SCHEMA ON DATABASE <データベース名A> TO ROLE DATABASE_ADMIN_ROLE;
GRANT CREATE SCHEMA ON DATABASE <データベース名B> TO ROLE DATABASE_ADMIN_ROLE;
-- 管理者ユーザー階層のWAREHOUSE_ADMIN_ROLEに権限付与
GRANT CREATE WAREHOUSE ON ACCOUNT TO ROLE WAREHOUSE_ADMIN_ROLE;
-- 管理者ユーザー階層のINTEGRATION_ADMIN_ROLEに権限付与
GRANT CREATE INTEGRATION ON ACCOUNT TO ROLE INTEGRATION_ADMIN_ROLE;
-- データベース・スキーマ作成-----------
USE ROLE DATABASE_ADMIN_ROLE;
-- データベースやスキーマを作成します
---------------------------------------
-- ウェアハウス作成---------------------
USE ROLE WAREHOUSE_ADMIN_ROLE;
-- ウェアハウスを作成します
---------------------------------------
-- ウェアハウスの仕様権限付与
USE ROLE WAREHOUSE_ADMIN_ROLE;
-- 一般ユーザー階層のアカウントロールにウェアハウスの権限付与
GRANT USAGE ON WAREHOUSE <ウェアハウス名> TO ROLE VIEW_ROLE_A;
GRANT USAGE ON WAREHOUSE <ウェアハウス名> TO ROLE VIEW_ROLE_B;
GRANT USAGE ON WAREHOUSE <ウェアハウス名> TO ROLE EDIT_ROLE_C;
GRANT USAGE ON WAREHOUSE <ウェアハウス名> TO ROLE EDIT_ROLE_D;
GRANT USAGE ON WAREHOUSE <ウェアハウス名> TO ROLE CREATE_ROLE_E;
GRANT USAGE ON WAREHOUSE <ウェアハウス名> TO ROLE CREATE_ROLE_F;
-- ##データベースロールへの権限付与
USE ROLE DATABASE_ADMIN_ROLE;
-- データベース権限のデータベースロールに権限付与
GRANT MONITOR ON DATABASE <データベース名A> TO DATABASE ROLE DB_A_PRIVILEGE;
GRANT MONITOR ON DATABASE <データベース名B> TO DATABASE ROLE DB_B_PRIVILEGE;
-- スキーマ権限のデータベースロールに権限付与
-- 参照権限
GRANT USAGE ON SCHEMA <データベース名A>.<スキーマ名A> TO DATABASE ROLE SCHEMA_A_VIEW_PRIVILEGE;
GRANT USAGE ON SCHEMA <データベース名A>.<スキーマ名B> TO DATABASE ROLE SCHEMA_B_VIEW_PRIVILEGE;
GRANT USAGE ON SCHEMA <データベース名B>.<スキーマ名C> TO DATABASE ROLE SCHEMA_C_VIEW_PRIVILEGE;
GRANT SELECT,REFERENCES ON ALL TABLES IN SCHEMA <データベース名A>.<スキーマ名A> TO DATABASE ROLE SCHEMA_A_VIEW_PRIVILEGE; -- テーブル作成していなければ不要
GRANT SELECT,REFERENCES ON ALL TABLES IN SCHEMA <データベース名A>.<スキーマ名B> TO DATABASE ROLE SCHEMA_B_VIEW_PRIVILEGE; -- テーブル作成していなければ不要
GRANT SELECT,REFERENCES ON ALL TABLES IN SCHEMA <データベース名B>.<スキーマ名C> TO DATABASE ROLE SCHEMA_C_VIEW_PRIVILEGE; -- テーブル作成していなければ不要
GRANT SELECT,REFERENCES ON ALL VIEWS IN SCHEMA <データベース名A>.<スキーマ名A> TO DATABASE ROLE SCHEMA_A_VIEW_PRIVILEGE; -- テーブル作成していなければ不要
GRANT SELECT,REFERENCES ON ALL VIEWS IN SCHEMA <データベース名A>.<スキーマ名B> TO DATABASE ROLE SCHEMA_B_VIEW_PRIVILEGE; -- テーブル作成していなければ不要
GRANT SELECT,REFERENCES ON ALL VIEWS IN SCHEMA <データベース名B>.<スキーマ名C> TO DATABASE ROLE SCHEMA_C_VIEW_PRIVILEGE; -- テーブル作成していなければ不要
GRANT SELECT,REFERENCES ON ALL MATERIALIZED VIEWS IN SCHEMA <データベース名A>.<スキーマ名A> TO DATABASE ROLE SCHEMA_A_VIEW_PRIVILEGE; -- テーブル作成していなければ不要
GRANT SELECT,REFERENCES ON ALL MATERIALIZED VIEWS IN SCHEMA <データベース名A>.<スキーマ名B> TO DATABASE ROLE SCHEMA_B_VIEW_PRIVILEGE; -- テーブル作成していなければ不要
GRANT SELECT,REFERENCES ON ALL MATERIALIZED VIEWS IN SCHEMA <データベース名B>.<スキーマ名C> TO DATABASE ROLE SCHEMA_C_VIEW_PRIVILEGE; -- テーブル作成していなければ不要
GRANT SELECT ON FUTURE TABLES IN SCHEMA <データベース名A>.<スキーマ名A> TO DATABASE ROLE SCHEMA_A_VIEW_PRIVILEGE ; -- 将来権限付与
GRANT SELECT ON FUTURE TABLES IN SCHEMA <データベース名A>.<スキーマ名B> TO DATABASE ROLE SCHEMA_B_VIEW_PRIVILEGE ; -- 将来権限付与
GRANT SELECT ON FUTURE TABLES IN SCHEMA <データベース名B>.<スキーマ名C> TO DATABASE ROLE SCHEMA_C_VIEW_PRIVILEGE ; -- 将来権限付与
GRANT SELECT ON FUTURE VIEWS IN SCHEMA <データベース名A>.<スキーマ名A> TO DATABASE ROLE SCHEMA_A_VIEW_PRIVILEGE ; -- 将来権限付与
GRANT SELECT ON FUTURE VIEWS IN SCHEMA <データベース名A>.<スキーマ名B> TO DATABASE ROLE SCHEMA_B_VIEW_PRIVILEGE ; -- 将来権限付与
GRANT SELECT ON FUTURE VIEWS IN SCHEMA <データベース名B>.<スキーマ名C> TO DATABASE ROLE SCHEMA_C_VIEW_PRIVILEGE ; -- 将来権限付与
GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN SCHEMA <データベース名A>.<スキーマ名A> TO DATABASE ROLE SCHEMA_A_VIEW_PRIVILEGE ; -- 将来権限付与
GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN SCHEMA <データベース名A>.<スキーマ名B> TO DATABASE ROLE SCHEMA_B_VIEW_PRIVILEGE ; -- 将来権限付与
GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN SCHEMA <データベース名B>.<スキーマ名C> TO DATABASE ROLE SCHEMA_C_VIEW_PRIVILEGE ; -- 将来権限付与
-- 編集権限
GRANT INSERT,UPDATE,TRUNCATE,DELETE ON ALL TABLES IN SCHEMA <データベース名A>.<スキーマ名A> TO DATABASE ROLE SCHEMA_A_EDIT_PRIVILEGE; -- テーブル作成していなければ不要
GRANT INSERT,UPDATE,TRUNCATE,DELETE ON ALL TABLES IN SCHEMA <データベース名A>.<スキーマ名B> TO DATABASE ROLE SCHEMA_B_EDIT_PRIVILEGE; -- テーブル作成していなければ不要
GRANT INSERT,UPDATE,TRUNCATE,DELETE ON ALL TABLES IN SCHEMA <データベース名B>.<スキーマ名C> TO DATABASE ROLE SCHEMA_C_EDIT_PRIVILEGE; -- テーブル作成していなければ不要
GRANT INSERT,UPDATE,TRUNCATE,DELETE ON FUTURE TABLES IN SCHEMA <データベース名A>.<スキーマ名A> TO DATABASE ROLE SCHEMA_A_EDIT_PRIVILEGE ; -- 将来権限付与
GRANT INSERT,UPDATE,TRUNCATE,DELETE ON FUTURE TABLES IN SCHEMA <データベース名A>.<スキーマ名B> TO DATABASE ROLE SCHEMA_B_EDIT_PRIVILEGE ; -- 将来権限付与
GRANT INSERT,UPDATE,TRUNCATE,DELETE ON FUTURE TABLES IN SCHEMA <データベース名B>.<スキーマ名C> TO DATABASE ROLE SCHEMA_C_EDIT_PRIVILEGE ; -- 将来権限付与
-- 作成権限
GRANT CREATE TABLE,CREATE VIEW,CREATE MATERIALIZED VIEW ON SCHEMA ARAKI_TOMOHITO.TEST_SCHEMA TO DATABASE ROLE SCHEMA_A_CREATE_PRIVILEGE;
GRANT CREATE TABLE,CREATE VIEW,CREATE MATERIALIZED VIEW ON SCHEMA ARAKI_TOMOHITO.TEST_SCHEMA2 TO DATABASE ROLE SCHEMA_B_CREATE_PRIVILEGE;
GRANT CREATE TABLE,CREATE VIEW,CREATE MATERIALIZED VIEW ON SCHEMA <データベース名B>.<スキーマ名C> TO DATABASE ROLE SCHEMA_C_CREATE_PRIVILEGE;
-- その他権限のデータベースロールに権限付与
GRANT CREATE PIPE ON SCHEMA <データベース名A>.<スキーマ名A> TO DATABASE ROLE SCHEMA_A_PIPE_PRIVILEGE;
GRANT CREATE STAGE ON ALL SCHEMAS IN DATABASE <データベース名A> TO DATABASE ROLE DB_A_STAGE_PRIVILEGE;
GRANT USAGE ON FUTURE STAGES IN DATABASE <データベース名A> TO DATABASE ROLE DB_A_STAGE_PRIVILEGE;
-- ##権限継承
-- 管理者ユーザー階層アカウントロールをSYSADMINに継承
GRANT ROLE DATABASE_ADMIN_ROLE TO ROLE SYSADMIN;
GRANT ROLE WAREHOUSE_ADMIN_ROLE TO ROLE SYSADMIN;
GRANT ROLE INTEGRATION_ADMIN_ROLE TO ROLE SYSADMIN;
-- 一般ユーザー階層アカウントロールをDATABASE_ADMIN_ROLEに継承
GRANT ROLE VIEW_ROLE_A TO ROLE DATABASE_ADMIN_ROLE;
GRANT ROLE VIEW_ROLE_B TO ROLE DATABASE_ADMIN_ROLE;
GRANT ROLE EDIT_ROLE_C TO ROLE DATABASE_ADMIN_ROLE;
GRANT ROLE EDIT_ROLE_D TO ROLE DATABASE_ADMIN_ROLE;
GRANT ROLE CREATE_ROLE_E TO ROLE DATABASE_ADMIN_ROLE;
GRANT ROLE CREATE_ROLE_F TO ROLE DATABASE_ADMIN_ROLE;
GRANT ROLE CREATE_ROLE_F TO ROLE INTEGRATION_ADMIN_ROLE;
-- DB権限階層データベースロールをSCHEMA権限階層データベースロールに継承
GRANT DATABASE ROLE DB_A_PRIVILEGE TO DATABASE ROLE SCHEMA_A_VIEW_PRIVILEGE;
GRANT DATABASE ROLE DB_A_PRIVILEGE TO DATABASE ROLE SCHEMA_B_VIEW_PRIVILEGE;
GRANT DATABASE ROLE DB_B_PRIVILEGE TO DATABASE ROLE SCHEMA_C_VIEW_PRIVILEGE;
-- SCHEMA権限階層データベースロールの参照、編集、作成権限の階層構造作成
GRANT DATABASE ROLE SCHEMA_A_VIEW_PRIVILEGE TO DATABASE ROLE SCHEMA_A_EDIT_PRIVILEGE;
GRANT DATABASE ROLE SCHEMA_B_VIEW_PRIVILEGE TO DATABASE ROLE SCHEMA_B_EDIT_PRIVILEGE;
GRANT DATABASE ROLE SCHEMA_C_VIEW_PRIVILEGE TO DATABASE ROLE SCHEMA_C_EDIT_PRIVILEGE;
GRANT DATABASE ROLE SCHEMA_A_EDIT_PRIVILEGE TO DATABASE ROLE SCHEMA_A_CREATE_PRIVILEGE;
GRANT DATABASE ROLE SCHEMA_B_EDIT_PRIVILEGE TO DATABASE ROLE SCHEMA_B_CREATE_PRIVILEGE;
GRANT DATABASE ROLE SCHEMA_C_EDIT_PRIVILEGE TO DATABASE ROLE SCHEMA_C_CREATE_PRIVILEGE;
-- SCHEMA権限階層データベースロールを一般ユーザー階層アカウントロールに継承
GRANT DATABASE ROLE SCHEMA_A_VIEW_PRIVILEGE TO ROLE VIEW_ROLE_A;
GRANT DATABASE ROLE SCHEMA_B_VIEW_PRIVILEGE TO ROLE VIEW_ROLE_B;
GRANT DATABASE ROLE SCHEMA_C_VIEW_PRIVILEGE TO ROLE VIEW_ROLE_B;
GRANT DATABASE ROLE SCHEMA_A_EDIT_PRIVILEGE TO ROLE EDIT_ROLE_C;
GRANT DATABASE ROLE SCHEMA_B_EDIT_PRIVILEGE TO ROLE EDIT_ROLE_C;
GRANT DATABASE ROLE SCHEMA_C_EDIT_PRIVILEGE TO ROLE EDIT_ROLE_D;
GRANT DATABASE ROLE SCHEMA_A_CREATE_PRIVILEGE TO ROLE CREATE_ROLE_E;
GRANT DATABASE ROLE SCHEMA_B_CREATE_PRIVILEGE TO ROLE CREATE_ROLE_E;
GRANT DATABASE ROLE SCHEMA_A_CREATE_PRIVILEGE TO ROLE CREATE_ROLE_F;
GRANT DATABASE ROLE SCHEMA_B_CREATE_PRIVILEGE TO ROLE CREATE_ROLE_F;
GRANT DATABASE ROLE SCHEMA_C_CREATE_PRIVILEGE TO ROLE CREATE_ROLE_F;
-- その他権限階層データベースロールを一般ユーザー階層アカウントロールに継承
GRANT DATABASE ROLE SCHEMA_A_PIPE_PRIVILEGE TO ROLE CREATE_ROLE_E;
GRANT DATABASE ROLE DB_A_STAGE_PRIVILEGE TO ROLE CREATE_ROLE_E;
GRANT DATABASE ROLE DB_A_STAGE_PRIVILEGE TO ROLE CREATE_ROLE_F;
おまけ
設定確認するときに使えるコマンドをおまけで載せおきます。
データベースロールはSnowsightのツリーに表示されないので、権限やロール割り当ての確認コマンドなどを覚えておく必要があります。
※アカウントロールに継承されたデータベースロールは、Snowsightのロールの権限一覧から確認することはできます。
-- ユーザー一覧表示
SHOW USERS;
-- 個別のユーザー情報表示（完全一致）
SHOW USERS LIKE '<ユーザー名>';
-- 個別のユーザー情報表示（前方一致）
SHOW USERS STARTS WITH '<ユーザー名>';
-- ユーザー一覧表示
SHOW GRANTS TO USER "<ユーザー名>";
-- ユーザー設定情報確認
DESCRIBE USER "<ユーザー名>";
-- ユーザーに割り当てられたロールを表示
SHOW GRANTS TO USER "<ユーザー名>";
-- ロールが割り当てられたユーザー・ロールを表示
SHOW GRANTS OF ROLE <ロール名>;
-- データベースロールの一覧を表示
SHOW DATABASE ROLES IN DATABASE <データベース名>;
-- データベースロールに付与された権限を表示
SHOW GRANTS TO DATABASE ROLE <データベースロール名>;
-- データベースロールが割り当てられたロールを表示
SHOW GRANTS OF DATABASE ROLE <データベースロール名>;
-- データベースロールに付与したFUTURE権限を表示
SHOW FUTURE GRANTS TO DATABASE ROLE <データベースロール名>;
まとめ
以上が今回ご紹介するロール設計についてでした。
あくまでロール設計の一例になりますので、要件によってロール構成は大きく変わってきます。
記事書きながらも、こういうときは違うロール設計や権限付与が良さそうだな。。と感じているのでいくらでも方法あるのかなと思います。（奥が深い。。。）
ロール設計する際のベースとしてでも使用していただければ幸いです。
